package org.smile.db;


import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.smile.db.handler.ResultSetMap;
import org.smile.db.sql.SQLRunner;
/**
 * 数据库操作工具类
 * @author strive
 *
 */
public class DbUtils {
	
	protected static final Logger logger=Logger.getLogger(DbUtils.class);
	/**
	 * 查询一条sql语句
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException 
	 * @throws Exception
	 */
	public static List query(Connection conn, String sql,Object... params) throws SQLException {
		List list = null;
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			list = tool.query(sql,params);
		}catch(SQLException e){
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		}
		finally {
			tool.closeConn();
		}
		return list;
	}
	/**
	 * 查询一条sql语句
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException 
	 * @throws Exception
	 */
	public static List queryTop(Connection conn, String sql,int size,Object... params) throws SQLException {
		List list = null;
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			list = tool.queryTop(sql,size,params);
		}catch(SQLException e){
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		}
		finally {
			tool.closeConn();
		}
		return list;
	}
	/**
	 * 查询一条sql语句前N条记录
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException 
	 * @throws Exception
	 */
	public static List queryTop(int dialect,Connection conn, String sql,int size,Object... params) throws SQLException {
		List list = null;
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			tool.setDbDialect(dialect);
			list = tool.queryTop(sql,size,params);
		}catch(SQLException e){
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		}
		finally {
			tool.closeConn();
		}
		return list;
	}
	/**
	 * 批量执行
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int[] batch(Connection conn,String sql, Object[][] params) throws SQLException {
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			return  tool.batch(sql, params);
		}catch(SQLException e){
			throw new SQLException("批量执行语句出错:"+sql+"出错"+e.getMessage());
		} finally {
			tool.closeConn();
		}
	}
	/**
	 * 执行一条语句
	 * @param conn
	 * @param sql
	 * @throws Exception 
	 * @throws Exception
	 */
	public static boolean execute(Connection conn,String sql,Object... params) throws SQLException {
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			return tool.execute(sql,params);
		} catch (SQLException e) {
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		} finally {
			tool.closeConn();
		}
	}

	/**
	 * 
	 * @param conn
	 * @param sql
	 * @throws Exception
	 */
	public static int executeUpdate(Connection conn,String sql,Object... params) throws SQLException {
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			return tool.executeUpdate(sql,params);
		} catch (SQLException e) {
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		} finally {
			tool.closeConn();
		}
	}
	/**
	 * 查询返回Map 
	 * @param conn 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static ResultSetMap queryForMap(Connection conn,String sql,Object... params) throws SQLException{
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			return (ResultSetMap) tool.queryForObject(sql,params);
		} catch (SQLException e) {
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		} finally {
			tool.closeConn();
		}
	}
	/**
	 * 分页查询
	 * @param conn
	 * @param params
	 * @param sql
	 * @param page
	 * @param size
	 * @param dialect
	 * @return
	 * @throws Exception
	 */
	public static PageModel queryPageSql(Connection conn,String sql,int page,int size,int dialect,Object... params) throws SQLException{
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			tool.setDbDialect(dialect);
			return tool.queryPageSql(sql, page, size,params);
		} catch (SQLException e) {
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		} finally {
			tool.closeConn();
		}
	}
	/**
	 * 分页查询
	 * @param conn
	 * @param params
	 * @param sql
	 * @param page
	 * @param size
	 * @param dialect
	 * @return
	 * @throws Exception
	 */
	public static PageModel queryPageSql(Connection conn,String sql,Map params,int page,int size,int dialect) throws SQLException{
		SQLRunner tool = null;
		try {
			tool = new SQLRunner(conn);
			tool.setDbDialect(dialect);
			return tool.queryPageSql(sql,params, page, size);
		} catch (SQLException e) {
			throw new SQLException("执行语句出错:"+sql+"出错"+e.getMessage());
		} finally {
			tool.closeConn();
		}
	}

	/**
	 * 关闭一个连接
	 * @param conn
	 */
	public static void closeConnection(Connection conn){
		try {
			conn.clearWarnings();
		} catch (SQLException e) {
			logger.error("关闭数据库连接出现错误",e);
		}
	}
}
